package edu.uoc.tdp.dal;

import edu.uoc.tdp.entity.User;
import edu.uoc.tdp.entity.Rol;
import edu.uoc.tdp.customException.EIssueException;
import edu.uoc.tdp.i18n.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author J2RS / Jordi Sanchez
 */
public class ManagementUsers {

   private ManagementDb mngBD = null;
   private Connection conexion = null;

    public ManagementUsers() throws EIssueException{
        
        try {
            mngBD = new ManagementDb();
            mngBD.connectDB();
            conexion = mngBD.getConnection();
        } catch (edu.uoc.tdp.common.EIssueException ex) {
            Logger.getLogger(ManagementUsers.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

    public ManagementUsers(ManagementDb mngDb) {
        mngBD = mngDb;
        conexion = mngDb.getConnection();
    }

    /**
     * Mètode que obte els usuari - taula users   (aqui potser faltaria el where dels que estan actius)
     * @return Lista de espais
     */
    public List<User> GetAllUser() throws EIssueException{
        List<User> listUser = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid, \"password\", \"name\", surname1, surname2, company, address, postal_code, city, telephone, mobile_phone, roleid, email, creation_date, creation_user, inactivation_date, inactivation_user, record_date  FROM users";                                 
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listUser.add(new User(rs.getString(1), rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10),rs.getInt(11),rs.getInt(12),rs.getString(13),rs.getDate(14),rs.getString(15),rs.getDate(16),rs.getString(17),rs.getDate(18)));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listUser;
    }


    /**
     * Mètode que obte una llista de usuaris per al grid, - taula users
     * @return Lista de espais
     */
    public List<User> GetAllUserTable() throws EIssueException{
        List<User> listUser = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid,\"name\", surname1, surname2, company, postal_code FROM users";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                //listUser.add(new User(rs.getString(1), rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),"",0,"",0,0,0,"",null,"",null,"",null));
                listUser.add(new User(rs.getString(1),"", rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),"",rs.getInt(6),"",0,0,0,"",null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listUser;
    }
    
    /**
     * Mètode que obte un usuari - taula users
     * @return Lista de espais
     */
    public List<User> GetUserById(String userId) throws EIssueException {
        List<User> listUser = new ArrayList();
        
        try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid, \"password\", \"name\", surname1, surname2, company, address, postal_code, city, telephone, mobile_phone, roleid, email, creation_date, creation_user, inactivation_date, inactivation_user, record_date  FROM users WHERE userid='" + userId + "'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listUser.add(new User(rs.getString(1), rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getInt(10),rs.getInt(11),rs.getInt(12),rs.getString(13),rs.getDate(14),rs.getString(15),rs.getDate(16),rs.getString(17),rs.getDate(18)));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listUser;
    }



    

    /**
     * Mètode comprova si existeix el registre i que obte un boolean - taula users
     * @return boolean
     */
    public boolean CheckExistsUser(String UserId) throws EIssueException {
        
        boolean Result=false;
        try {           
                        
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid FROM users WHERE userid='" + UserId + "'";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                 Result=true;        
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return Result;
    }
    
    
    /**
     * Mètode que obte una llista de usuaris, filtrats per ID - taula users
     * @return Lista de espais
     */
    public List<User> GetUse_FindById(String userId) throws EIssueException {
        List<User> listUser = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid,\"name\", surname1, surname2, company, postal_code FROM users  WHERE userid like '%" + userId + "%'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listUser.add(new User(rs.getString(1),"", rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),"",rs.getInt(6),"",0,0,0,"",null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listUser;
    }

    /**
     * Mètode que obte una llista de usuaris, filtrats per Codi Postal - taula users
     * @return Lista de espais
     */
    public List<User> GetUse_FindByPostalCode(Integer PostalCode) throws EIssueException {
        List<User> listUser = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid,\"name\", surname1, surname2, company, postal_code FROM users  WHERE postal_code=" + String.valueOf(PostalCode);
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listUser.add(new User(rs.getString(1),"", rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),"",rs.getInt(6),"",0,0,0,"",null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listUser;
    }    
    

    /**
     * Mètode que obte una llista de usuaris, filtrats per NOM - taula users
     * @return Lista de espais
     */
    public List<User> GetUse_FindByName(String userName) throws EIssueException {
        List<User> listUser = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid,\"name\", surname1, surname2, company, postal_code FROM users  WHERE \"name\" like '%" + userName + "%'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listUser.add(new User(rs.getString(1),"", rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),"",rs.getInt(6),"",0,0,0,"",null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listUser;
    }
    
    /**
     * Mètode que obte una llista de usuaris, filtrats per company - taula users
     * @return Lista de espais
     */
    public List<User> GetUse_FindByCompany(String Company) throws EIssueException {
        List<User> listUser = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT userid,\"name\", surname1, surname2, company, postal_code FROM users  WHERE company like '%" + Company + "%'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listUser.add(new User(rs.getString(1),"", rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),"",rs.getInt(6),"",0,0,0,"",null,"",null,"",null));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listUser;
    }
    
    
     /**
     * Mètode que permet fer login - taula users
     * @return Lista de espais
     * @author tomeu
     */
    public User Login(String email, String password) throws EIssueException {
        User availableUser=null;
        
        try {   
            String sql = null;
            sql = "SELECT * FROM users WHERE email='"+email+"' AND password='"+password+"' and inactivation_user is NULL"; // JORDI
            Statement stmt = null;
            ResultSet rs = null;
            stmt = this.conexion.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next() ) {  
                String userId=rs.getString("userid");
                String userPassword = rs.getString("password");
                String name = rs.getString("name");
                String surname1 = rs.getString("surname1");
                String surname2 = rs.getString("surname2");
                String company = rs.getString("company");
                String address = rs.getString("address");
                int postalCode = rs.getInt("postal_code");
                String city = rs.getString("city");
                int telephone = rs.getInt("telephone");
                int mobilePhone = rs.getInt("mobile_phone");
                int roleId = rs.getInt("roleid");
                String userEmail = rs.getString("email");
                Date creationDate = rs.getDate("creation_date");
                String creationUser = rs.getString("creation_user");
                Date recordDate = rs.getDate("record_date");
                availableUser = new User(userId, userPassword, name, surname1, surname2, company, address, postalCode, city, telephone, mobilePhone, roleId, email, creationDate, creationUser, null, null, null);
            }  
            rs.close();
            stmt.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return availableUser;
    }

    

    
    
    /**
     * Mètode que guarda dades (Update) un usuari - taula users
     * @return boolea
     */        
    public boolean SaveUser(User User) throws EIssueException{
        boolean PROCESS_STATE = false;
        try {
            String insertQuery = "INSERT INTO users(userid, \"password\", \"name\", surname1, surname2, company, address, postal_code, city, telephone, mobile_phone, roleid, email, creation_date, creation_user)  VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?,?)";
            PreparedStatement command = this.mngBD.prepareCommand(insertQuery);
            command.setString(1, User.getUserId());
            command.setString(2, User.getPassword());
            command.setString(3, User.getName());
            command.setString(4, User.getSurname1());
            command.setString(5, User.getSurname2());
            command.setString(6, User.getCompany());
            command.setString(7, User.getAddress());
            command.setInt(8, User.getPostalCode());
            command.setString(9, User.getCity());
            command.setInt(10, User.getTelephone());
            command.setInt(11, User.getMobilePhone());
            command.setInt(12, User.getRoleId());
            command.setString(13, User.getEmail());
            command.setDate(14, new java.sql.Date(System.currentTimeMillis()));
            command.setString(15, User.getCreationUser());
            //command.setDate(16, new java.sql.Date(System.currentTimeMillis()));            
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return PROCESS_STATE;
    }

    /**
     * Mètode que fa Update de un usuari - taula users
     * @return boolea
     */     
    public boolean SaveUserChanges(User User) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            String updateQuery = "Update Users SET \"name\"=?, surname1=?, surname2=?, company=?, address=?, postal_code=?, city=?, telephone=?, mobile_phone=?, roleid=?, email=?  WHERE userid=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);            
            command.setString(1, User.getName());
            command.setString(2, User.getSurname1());
            command.setString(3, User.getSurname2());
            command.setString(4, User.getCompany());
            command.setString(5, User.getAddress());
            command.setInt(6, User.getPostalCode());
            command.setString(7, User.getCity());
            command.setInt(8, User.getTelephone());
            command.setInt(9, User.getMobilePhone());
            command.setInt(10, User.getRoleId());
            command.setString(11, User.getEmail());
            command.setString(12, User.getUserId());
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

    
    /**
     * Mètode que fa Update de un usuari - taula users
     * @return boolea
     */     
    public boolean SavePassword(String UserId, String Passw) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            String updateQuery = "Update Users SET \"password\"=? WHERE userid=?";
            //String updateQuery = "Update Users SET \"password\"='"+Passw+"' WHERE userid='" + UserId + "'";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);            
            command.setString(1, Passw);
            command.setString(2, UserId);            
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }
    
    /**
     * Mètode que fa dona de baixa/desactiva un usuari - taula users
     * @return boolea
     */      
    public boolean SaveInactivationUser(String InactivatUser, String userId) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            if (InactivatUser==null) InactivatUser=userId;           
            String updateQuery = "UPDATE users SET inactivation_date=now(),inactivation_user=?  WHERE userid=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);
            command.setString(1, InactivatUser);
            command.setString(2, userId);
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

      
    
public List<Rol> GetAllRoles() throws EIssueException{
        
       List<Rol> listAllRolles = new ArrayList();

       try {
            //Statement command = this.mngBD.createCommand();
           Statement command = null;
           command = this.conexion.createStatement();
            String select = "SELECT roleid,roledescription,record_date  FROM all_roles";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                
                int a = rs.getInt(1);
                String b = rs.getString(2);
                Date d = rs.getDate(3);
                Rol FilaRol;
                FilaRol = new Rol(rs.getInt(1), rs.getString(2), rs.getDate(3));
                listAllRolles.add(FilaRol);
                
                //listAllRolles.add(new Rol(rs.getInt(1), rs.getString(2), rs.getDate(3)));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listAllRolles;
    }

    
}
